<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>Section 12.6.&nbsp; Deleting Data</title>
<link rel="STYLESHEET" type="text/css" href="images/style.css">
<link rel="STYLESHEET" type="text/css" href="images/docsafari.css">
<script type="text/javascript"><!--
google_ad_client = "pub-0203281046321155";
google_alternate_ad_url = "http://www.bookhtml.com/adbrite.htm";
google_ad_width = 728;
google_ad_height = 90;
google_ad_format = "728x90_as";
google_ad_type = "text_image";
google_ad_channel ="4867465545";
google_color_border = "FFFFFF";
google_color_link = "0000FF";
google_color_bg = "FFFFFF";
google_color_text = "000000";
google_color_url = "0000FF";
//--></script>
<script type="text/javascript"
  src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12-SECT-5.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12-SECT-7.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<br><table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><TD valign="top"><a name="learnphpmysql-CHP-12-SECT-6"></a>
<h3 id="title-IDAILVJ" class="docSection1Title">12.6. Deleting Data</h3>
<p class="docText">Use the <tt>DELETE</tt> command to completely remove existing data from the database. Remember though, once you've deleted data, it can no longer be retrieved; it is permanently gone. Make sure you have appropriate checks and balances in place for the deletion of existing data. Use the <tt>WHERE</tt> command so that you don't delete data from all the rows in your table.</p>
<p class="docText">The command <tt>trUNCATE TABLE</tt> <tt><i>`tablename</i></tt>` deletes an entire table, which means the table structure and the records, and then recreates the structure. Technically, your final result is the same, but our example is a safer way to perform a delete. The advantage of <tt>trUNCATE</tt> is that it's much faster for deleting large tables.</p>
<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><tr><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><tr><TD width="60" valign="top"><img src="images/warning_yellow.jpg" width="51" height="36" alt=""></td><td valign="top">
<p class="docText">While it's great to be able to <tt>DROP</tt> and <tt>TRUNCATE</tt> tables from PHP, you probably don't want to leave this capability anywhere on your web site for an average user.</P>
</td></TR></table></td></tr></table></p><BR>
<p class="docText">There is a way to safeguard against erroneous selections by running the query using <tt>SELECT</tt> instead of <tt>DELETE</tt>. Deleting data from a MySQL database through PHP works similarly to any of the other queries. If you do this, query results display which row or rows are going to be affected by your deletion. Let's modify the example to provide a link that deletes the current row. In <a class="docLink" href="#learnphpmysql-CHP-12-EX-9">Example 12-9</a>, you'll delete a purchase.</p>
<a name="learnphpmysql-CHP-12-EX-9"></a><H5 id="title-IDAWMVJ" class="docExampleTitle">Example 12-9. Providing a link to delete a purchase in deletion_link.php</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><TD>

<pre>
&lt;?php
require_once('db_login.php');
require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: &lt;br /&gt;". DB::errorMessage($connection));
}
$query = "SELECT * FROM `purchases` NATURAL JOIN `books`";
$result = $connection-&gt;query($query);
if (DB::isError($result)){
die("Could not query the database: &lt;br /&gt;".$query." ".DB::errorMessage($result));
}
echo '&lt;table border="1"&gt;';
echo "&lt;tr&gt;&lt;th&gt;User&lt;/th&gt;&lt;th&gt;Title&lt;/th&gt;&lt;th&gt;Purchased&lt;/th&gt;&lt;th&gt;Remove&lt;/th&gt;&lt;/tr&gt;";
while ($result_row = $result-&gt;fetchRow(DB_FETCHMODE_ASSOC)) {
echo "&lt;tr&gt;&lt;td&gt;";
echo $result_row["user_id"] . '&lt;/td&gt;&lt;td&gt;';
echo $result_row["title"] . '&lt;/td&gt;&lt;td&gt;';
echo $result_row["purchased"] . '&lt;/td&gt;&lt;td&gt;';
<span class="docEmphStrong">echo '&lt;a href="delete.php?purchase_id='.$result_row["purchase_id"].'"&gt;Click
to remove from purchases&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;';</span>
}
echo '&lt;/table&gt;';
$connection-&gt;disconnect();
?&gt;
</pre><br>

</TD></tr></table></p>
<p class="docText">In <a class="docLink" href="#learnphpmysql-CHP-12-EX-9">Example 12-9</a>, you're using the <tt>SELECT</tt> command so that data won't be erroneously deleted when the query is run. The results of this query display which row(s) are affected by the deletion. The script that handles the actual deletion is shown in <a class="docLink" href="#learnphpmysql-CHP-12-EX-10">Example 12-10</a>.</P>
<a name="learnphpmysql-CHP-12-EX-10"></a><H5 id="title-IDATNVJ" class="docExampleTitle">Example 12-10. The delete.php code for performing a delete</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
&lt;?php
require_once('db_login.php');
require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: &lt;br /&gt;". DB::errorMessage($connection));
}
<span class="docEmphStrong">$purchase_id = $_GET["purchase_id"];
$query = "DELETE FROM `purchases` WHERE `purchase_id`=$purchase_id";</span>
$result = $connection-&gt;query($query);
if (DB::isError($result)){
die("Could not query the database: &lt;br /&gt;".$query." ".DB::errorMessage($result));
}
?&gt;
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Item deleted!&lt;/title&gt;
&lt;meta http-equiv="refresh" content="4; url=deletion_link.php"&gt;
&lt;/head&gt;
&lt;body&gt;
Item deleted!&lt;br /&gt;
&lt;?php
$query = "SELECT * FROM `purchases` NATURAL JOIN `books` NATURAL JOIN `authors`";
$result = $connection-&gt;query($query);
if (DB::isError($result)){
die("Could not query the database: &lt;br /&gt;".$query." ".DB::errorMessage($result));
}
echo '&lt;table border="1"&gt;';
echo "&lt;tr&gt;&lt;th&gt;User&lt;/th&gt;&lt;th&gt;Title&lt;/th&gt;&lt;th&gt;Pages&lt;/th&gt;";
echo "&lt;th&gt;Author&lt;/th&gt;&lt;th&gt;Purchased&lt;/th&gt;&lt;/tr&gt;";
while ($result_row = $result-&gt;fetchRow(DB_FETCHMODE_ASSOC)) {
echo "&lt;tr&gt;&lt;td&gt;";
echo $result_row["user_id"] . '&lt;/td&gt;&lt;td&gt;';
echo $result_row["title"] . '&lt;/td&gt;&lt;td&gt;';
echo $result_row["pages"] . '&lt;/td&gt;&lt;td&gt;';
echo $result_row["author"] . "&lt;/td&gt;&lt;td&gt;";
echo $result_row["purchased"] . "&lt;/td&gt;&lt;/tr&gt;";
}
echo "&lt;/table&gt;";
$connection-&gt;disconnect();
?&gt;
&lt;/body&gt;
&lt;/html&gt;
</pre><BR>

</td></TR></table></p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-12-FIG-10">Figure 12-10</a> shows how the browser window looks after going to <span class="docEmphasis">deletion_link.php</span>.</p>
<a name="learnphpmysql-CHP-12-FIG-10"></a><P><center>
<H5 class="docFigureTitle">Figure 12-10. Each purchase has a link for its removal</h5>
<img border="0" alt="" width="549" height="184" SRC="images/learnphpmysql_1210.jpg">
</center></P><br>
<p class="docText">Click on the last removal link to see <a class="docLink" href="#learnphpmysql-CHP-12-FIG-11">Figure 12-11</a>.</p>
<a name="learnphpmysql-CHP-12-FIG-11"></a><p><center>
<h5 class="docFigureTitle">Figure 12-11. A successful delete, and the book is removed from the purchases</h5>
<img border="0" alt="" width="549" height="202" SRC="images/learnphpmysql_1211.jpg">
</center></p><br>
<p class="docText">The purchase is no longer in the table. It's a good idea to confirm with the user before completing a deletion. This is usually handled by an intermediate screen that summarizes what's going to be deleted and requires the user to click a button that confirms the deletion.</p>
<a name="learnphpmysql-CHP-12-SECT-6.1"></a>
<h4 id="title-IDAMPVJ" class="docSection2Title">12.6.1. Generating Unique Identifiers</h4>
<a name="IDX-CHP-12-0601"></a> 

<p class="docText">In our examples so far, we always let MySQL pick the primary key when doing inserts by sending <tt>NULL</tt> in the <tt>key</tt> field. The downside of this is that you don't know what key value MySQL assigned your row. If you add a book and then an author, how do you know what the foreign key value is for the book to add in the <tt>authors</tt> table? Well, you can use the <tt>mysql_insert_id</tt> command to get the last auto-assigned primary key from an <tt>AUTO_INCREMENT</tt> column.</p>
<p class="docText">Its syntax is:</p>
<pre>
int mysql_insert_id ( [resource link_identifier] )
</pre><BR>

<p class="docText">If the last query generated an auto-increment, that value is returned. Zero is returned if the last query didn't generate a key. <tt>FALSE</tt> is returned if there isn't a valid database connection.</p>
<p class="docText">We'll add a title and an author in <a class="docLink" href="#learnphpmysql-CHP-12-EX-11">Example 12-11</a>.</p>
<a name="learnphpmysql-CHP-12-EX-11"></a><H5 id="title-IDAXQVJ" class="docExampleTitle">Example 12-11. Using mysql_insert_id to link up an author to a title</H5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><td>

<pre>
&lt;?php
require_once('db_login.php');
require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: &lt;br /&gt;". DB::errorMessage($connection));
}
$query = "INSERT INTO `books` VALUES (NULL,'Python in a Nutshell',600)";
$result = $connection-&gt;query($query);
if (DB::isError($result)){
die("Could not query the database: &lt;br /&gt;".$query." ".DB::errorMessage($result));
}
<span class="docEmphStrong">$last_value = mysql_insert_id();</span>
echo "The id that was created is: $last_value&lt;br /&gt;";
$query = "INSERT INTO `authors` VALUES (NULL,$last_value,'Alex Martelli')";
$result = $connection-&gt;query($query);
if (DB::isError($result)){
die("Could not query the database: &lt;br /&gt;".$query." ".DB::errorMessage($result));
}
echo "Inserted successfully!";
$connection-&gt;disconnect();
?&gt;
</pre><br>

</td></TR></table></p>
<p class="docText">Execute <tt>mysql_insert_id</tt> directly after the <tt>INSERT</tt> statement to minimize the possibility of another <tt>INSERT</tt> statement being executed before you read the value. In a multitasking environment, you have to be aware that other processes or users may also be using the data to execute queries. <a class="docLink" href="#learnphpmysql-CHP-12-FIG-12">Figure 12-12</a> shows the output of the PHP code.</P>
<a name="learnphpmysql-CHP-12-FIG-12"></a><p><center>
<h5 class="docFigureTitle">Figure 12-12. We can see that the book was assigned a key value of 9</H5>
<img border="0" alt="" width="466" height="125" SRC="images/learnphpmysql_1212.jpg">
</center></P><br>
<p class="docText">Let's check <a class="docLink" href="#learnphpmysql-CHP-12-FIG-13">Figure 12-13</a> to make sure that the values were saved correctly in the database by selecting from both tables in the <tt>mysql</tt> command-line client.</P>
<a name="learnphpmysql-CHP-12-FIG-13"></a><p><center>
<h5 class="docFigureTitle">Figure 12-13. Our new entries for the book and author are present</H5>
<img border="0" alt="" width="549" height="198" SRC="images/learnphpmysql_1213.jpg">
</center></P><br>
<p class="docText">The <tt>title_id</tt> value of 9 was correctly added to the <tt>authors</tt> table.</p>


<a href="11011536.html"><img src="images/pixel.jpg" alt="" width="1" height="1" border="0"></a></TD></TR></table>
<br>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12-SECT-5.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12-SECT-7.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
</body></html>
